package com.sinodata.bsm.center.dao.impl;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.sinodata.bsm.center.bean.Condition;
import com.sinodata.bsm.center.bean.EventBean;
import com.sinodata.bsm.center.bean.Page;
import com.sinodata.bsm.center.bean.ReduceEventDynamicBean;
import com.sinodata.bsm.center.dao.BaseDao;
import com.sinodata.bsm.center.dao.EventDao;
import com.sinodata.bsm.common.constants.EventConstants;
import com.sinodata.bsm.common.constants.RelationConstants;
import com.sinodata.bsm.common.vo.Event;
import com.sinodata.bsm.common.vo.EventCauses;
import com.sinodata.bsm.common.vo.EventRemark;

/**
 * <p>
 * Description: 
 * </p>
 *
 * @author liulibo
 * @version 1.0

 * <p>
 * History: 
 *
 * Date                     Author         Version     Description
 * ---------------------------------------------------------------------------------
 * 2012-5-25 AM 10:07:48    liulibo        1.0         To create
 * </p>
 *
 * @since 
 * @see     
 */
@Repository
public class EventDaoImpl extends BaseDao implements EventDao {

    private final static Logger logger = LoggerFactory.getLogger(EventDaoImpl.class);

    @Override
    public Page<EventBean> findEventByPage(Page<EventBean> page, Condition condition, String resTypeId, List<String> dimensionArray) {
        try {

            String format = "yyyy-MM-dd HH:mm:ss";
            SimpleDateFormat simple = new SimpleDateFormat(format);
            page.setTotal(getTotal(condition, resTypeId, dimensionArray));
            StringBuffer sql = new StringBuffer("select e.id,");
            sql.append("e.res_id,");
            sql.append("r.name,");
            sql.append("e.event_type_id,");
            sql.append("etype.name etname,");
            sql.append("e.msg,");
            sql.append("e.level_,");
            sql.append("to_char(e.occur_time,'yyyy-MM-dd hh24:mi:ss'),");
            sql.append("e.count,");
            sql.append("e.ack_user,");
            sql.append("to_char(e.ack_time,'yyyy-mm-dd hh24:mi:ss'),");
            sql.append("to_char(e.last_occur_time,'yyyy-MM-dd hh24:mi:ss'),");
            sql.append("e.status,");
            sql.append("to_char(e.confirm_time,'yyyy-MM-dd hh24:mi:ss'),");
            sql.append("e.confirmer,");
            sql.append("ec.title,");
            sql.append("r.ip,");
            sql.append("e.confirm_type");
            sql.append(" from event e ");
            sql.append(" left outer join event_type etype on e.event_type_id = etype.id");
            sql.append(" left outer join res r on e.res_id=r.id");
            sql.append(" left outer join event_causes ec on e.id=ec.event_id");
            sql.append(" where 1 = 1  and r.deleted = 0 ");
            sql.append(getConditonSql(condition));
            sql.append(getResTypeSql(resTypeId));
            sql.append(getDimensionSql(dimensionArray));
            sql.append(getOrderSql(page));
            System.out.println(sql.toString());
            Query query = getSession().createSQLQuery(sql.toString()).setFirstResult(page.getFirstResultNo()).setMaxResults(page.getPageSize());
            List<Object[]> list = query.list();
            List<EventBean> listResult = new ArrayList<EventBean>();
            for (Object[] o : list) {
                EventBean eventBean = new EventBean();
                Event event = new Event();
                event.setId(Long.valueOf(String.valueOf(o[0])));
                event.setResId(Long.valueOf(String.valueOf(o[1])));
                eventBean.setResName(String.valueOf(o[2]));
                eventBean.setEventTypeName(String.valueOf(o[4]));
                event.setMsg(String.valueOf(o[5]));
                event.setLevel(Integer.valueOf(String.valueOf(o[6])));
                event.setOccurTime(simple.parse(String.valueOf(o[7])));
                if (o[8] != null && !String.valueOf(o[8]).equals("")) {
                    event.setCount(Integer.valueOf(String.valueOf(o[8])));
                }
                if (o[9] != null && !String.valueOf(o[9]).equals("") && o[10] != null && !String.valueOf(o[10]).equals("")) {
                    eventBean.setAck(String.valueOf(o[9]) + " " + String.valueOf(o[10]));
                }
                if (o[11] != null && !String.valueOf(o[11]).equals("")) {
                    eventBean.setLastOccurTime(String.valueOf(o[11]));
                }
                if (o[14] != null) {
                    event.setConfirmer(String.valueOf(o[14]));
                }
                event.setStatus(Integer.valueOf(String.valueOf(o[12])));
                eventBean.setConfirmTime(String.valueOf(o[13]));
                if (o[13] != null && !String.valueOf(o[13]).equals("") && o[14] != null && !String.valueOf(o[14]).equals("")) {
                    eventBean.setConfirm(String.valueOf(o[14]) + " " + String.valueOf(o[13]));
                }
                eventBean.setCauses(String.valueOf(o[15]));
                eventBean.setIp(String.valueOf(o[16]));
                eventBean.setEvent(event);
                listResult.add(eventBean);
            }
            page.setRows(listResult);
        } catch (Exception e) {
            logger.error("", e);
        }
        return page;
    }

    /**
     * 根据条件查询事件集合
     */
    @Override
    public List<EventBean> findEventByCondition(Condition condition, String resTypeId, List<String> dimensionArray) {
        List<EventBean> listResult = new ArrayList<EventBean>();
        String format = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat simple = new SimpleDateFormat(format);
        try {
            StringBuffer sql = new StringBuffer("select e.id,");
            sql.append("e.res_id,");
            sql.append("r.name,");
            sql.append("e.event_type_id,");
            sql.append("etype.name etname,");
            sql.append("e.msg,");
            sql.append("e.level_,");
            sql.append("to_char(e.occur_time,'yyyy-MM-dd hh24:mi:ss'),");
            sql.append("e.count,");
            sql.append("e.ack_user,");
            sql.append("to_char(e.ack_time,'yyyy-mm-dd hh24:mi:ss'),");
            sql.append("to_char(e.last_occur_time,'yyyy-MM-dd hh24:mi:ss'),");
            sql.append("e.confirmer,");
            sql.append("to_char(e.confirm_time,'yyyy-MM-dd hh24:mi:ss'),");
            sql.append("e.status,");
            sql.append("ec.title,");
            sql.append("r.ip,");
            sql.append("e.confirm_type");
            sql.append(" from event e ");
            sql.append(" left outer join event_type etype on e.event_type_id = etype.id");
            sql.append(" left outer join res r on e.res_id=r.id");
            sql.append(" left outer join event_causes ec on e.id=ec.event_id");
            sql.append(" where 1 = 1 and r.deleted = 0");
            sql.append(getConditonSql(condition));
            sql.append(getResTypeSql(resTypeId));
            sql.append(getDimensionSql(dimensionArray));
            sql.append(" order by e.level_ desc, e.occur_time desc");
            Query query = getSession().createSQLQuery(sql.toString());
            List<Object[]> list = query.list();
            for (Object[] o : list) {
                EventBean eventBean = new EventBean();
                Event event = new Event();
                event.setId(Long.valueOf(String.valueOf(o[0])));
                event.setResId(Long.valueOf(String.valueOf(o[1])));
                eventBean.setResName(String.valueOf(o[2]));
                event.setEventTypeId(Long.valueOf(String.valueOf(o[3])));
                eventBean.setEventTypeName(String.valueOf(o[4]));
                event.setMsg(String.valueOf(o[5]));
                event.setLevel(Integer.valueOf(String.valueOf(o[6])));
                if (o[7] != null) {
                    event.setOccurTime(simple.parse(String.valueOf(o[7])));
                    eventBean.setOccurTime(String.valueOf(o[7]));
                }
                if (o[8] != null && !String.valueOf(o[8]).equals("")) {
                    event.setCount(Integer.valueOf(String.valueOf(o[8])));
                }
                if (o[9] != null) {
                    event.setAckUser(String.valueOf(o[9]));
                }
                if (o[10] != null) {
                    event.setAckTime(simple.parse(String.valueOf(o[10])));
                    eventBean.setAckTime(String.valueOf(o[10]));
                }
                if (o[9] != null && !String.valueOf(o[9]).equals("") && o[10] != null && !String.valueOf(o[10]).equals("")) {
                    eventBean.setAck(String.valueOf(o[9]) + " " + String.valueOf(o[10]));
                }
                if (o[11] != null) {
                    event.setLastOccurTime(simple.parse(String.valueOf(o[11])));
                    eventBean.setLastOccurTime(String.valueOf(o[11]));
                }
                if (o[12] != null) {
                    event.setConfirmer(String.valueOf(o[12]));
                }
                if (o[13] != null) {
                    event.setConfirmTime(simple.parse(String.valueOf(o[13])));
                    eventBean.setConfirmTime(String.valueOf(o[13]));
                }
                event.setStatus(Integer.valueOf(String.valueOf(o[14])));
                if (o[15] != null) {
                    eventBean.setCauses(String.valueOf(o[15]));
                }
                eventBean.setIp(String.valueOf(o[16]));
                if (o[17] != null) {
                    event.setConfirmType(Integer.valueOf(String.valueOf(o[17])));
                }
                eventBean.setEvent(event);
                listResult.add(eventBean);
            }
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
        return listResult;
    }

    private int getTotal(Condition condition, String resTypeId, List<String> dimensionArray) {
        StringBuilder hql = new StringBuilder("  select count(*) from Event e left join event_type etype on e.event_type_id = etype.id left join res r on e.res_id=r.id where r.deleted = 0");
        hql.append(getConditonSql(condition));
        hql.append(getResTypeSql(resTypeId));
        hql.append(getDimensionSql(dimensionArray));
        Query query = this.getSession().createSQLQuery(hql.toString());
        return ((BigDecimal) query.list().iterator().next()).intValue();
    }

    /**
     * 获取排序sql
     * 
     * @param page
     * @return
     */
    private StringBuilder getOrderSql(Page<EventBean> page) {
        StringBuilder hql = new StringBuilder();
        if (page.getOrder() == null || page.getOrder().size() == 0) {
            hql.append(" order by e.level_ desc, e.OCCUR_TIME desc");
        } else {
            hql.append(" order by ");
            Map<String, String> orderMap = page.getOrder();
            Set<String> orderSet = orderMap.keySet();
            for (String order : orderSet) {
                hql.append(order + " " + orderMap.get(order) + ",");
            }
            hql.replace(hql.length() - 1, hql.length(), " ");
        }
        return hql;
    }

    private StringBuilder getResTypeSql(String resTypeId) {
        StringBuilder sb = new StringBuilder();
        if (resTypeId != null && !"".equals(resTypeId)) {
            sb.append(" and e.res_id in(");
            sb.append(" select distinct id from res res where res.res_type_id in (");
            sb.append(" select id from res_type start with id='");
            sb.append(resTypeId);
            sb.append("' connect by prior id=parent_id");
            sb.append(" ))");
        }
        return sb;
    }

    private StringBuilder getDimensionSql(List<String> dimensionArray) {
        StringBuilder sb = new StringBuilder();
        if (dimensionArray.size() != 0) {
            sb.append(" and e.res_id in (");
            sb.append(" select distinct res_id from res_attr_val where (");
            for (String dimension : dimensionArray) {
                sb.append("attr_id in(select id from attribute where data_type_id=5 and data_unit=1) and attr_value in(");
                sb.append("select to_char(car.id) from ci_attr_range car start with id='");
                sb.append(dimension);
                sb.append("' connect by prior id=parent_id");
                sb.append(") and ");
            }
            sb.delete(sb.lastIndexOf(" and"), sb.length());
            sb.append("))");
        }
        return sb;
    }

    private StringBuilder getConditonSql(Condition condition) {
        StringBuilder hql = new StringBuilder();
        Map<String, String> condMap = condition.getWhere();
        if (condMap.size() != 0) {
            hql.append(" and ( ");
            Iterator<String> keyItr = condMap.keySet().iterator();
            while (keyItr.hasNext()) {
                String key = keyItr.next();
                if (key.equals("level_")) {
                    if (!condMap.get(key).contains(",")) {
                        hql.append(key).append(" in (" + condMap.get(key) + ")");
                    } else {
                        hql.append(key).append(" in (");
                        String[] str = condMap.get(key).split(",");
                        for (int i = 0; i < str.length; i++) {
                            hql.append(str[i]).append(",");
                        }
                        hql.delete(hql.lastIndexOf(","), hql.length()).append(" ) ");
                    }
                } else if (key.equals("resIds")) {
                    String[] str = condMap.get(key).split(",");
                    StringBuffer sb = new StringBuffer();
                    for (int i = 0; i < str.length; i++) {
                        sb.append(str[i]).append(",");
                    }
                    sb.delete(sb.lastIndexOf(","), sb.length());
                    hql.append(" e.res_id in ( select id from res where id in (");
                    hql.append(sb);
                    hql.append(") union select end_id  from res_relation start with start_id in ( ");
                    hql.append(sb);
                    hql.append(") and relation_type_id  in ( select  id from relation_type  start with id = 10 connect by prior  id = parent ) connect by prior  end_id = start_id");
                    hql.append(")");
                } else if (key.equals("res_type_id")) {
                    if (!condMap.get(key).contains(",")) {
                        hql.append("etype.").append(key).append(" in (" + condMap.get(key) + ")");
                    } else {
                        hql.append("etype.").append(key).append(" in (");
                        String[] str = condMap.get(key).split(",");
                        for (int i = 0; i < str.length; i++) {
                            hql.append(str[i]).append(",");
                        }
                        hql.delete(hql.lastIndexOf(","), hql.length()).append(" ) ");
                    }
                } else if (key.equals("status")) {
                    if (condMap.get(key) != null) {
                        if (condMap.get(key).contains(",")) {
                            hql.append(key).append(" in ( ");
                            for (String status : condMap.get(key).split(",")) {
                                hql.append(status).append(",");
                            }
                            hql.delete(hql.lastIndexOf(","), hql.length());
                            hql.append(")");
                        } else {
                            hql.append(key).append(" = ").append(condMap.get(key));
                        }
                    }
                } else if (key.equals("beginDate")) {
                    hql.append("occur_time").append(" >= to_date('" + condMap.get(key) + "','yyyy-MM-dd hh24:mi')");
                } else if (key.equals("endDate")) {
                    hql.append("occur_time").append(" <= to_date('" + condMap.get(key) + "','yyyy-MM-dd hh24:mi')");
                } else if (key.equals("homologous")) {
                    hql.append("e.").append(condMap.get(key));
                } else if (key.equals("resId")) {
                    hql.append(" e.res_id in (");
                    Integer parentResId = Integer.parseInt(condMap.get(key));
                    hql.append("  select t.end_id from res_relation t  start with t.start_id=" + parentResId + " connect by prior t.end_id=t.start_id  ");
                    hql.append(" union select " + parentResId + " from dual) and e.status<>2");
                } else if (key.equals("motiTypeId")) {
                    hql.append(" (e.res_id in ( ");
                    hql.append(" select id from res where deleted = 0 and res_type_id in ( ");
                    hql.append(" select id  from res_type start with id in ( select res_type_id ");
                    hql.append(" from moti_restype_rel t where moit_type_id = " + condMap.get(key) + " )");
                    hql.append(" connect by prior id = parent_id ) ");
                    hql.append(" ) )");
                } else if (key.equals("confirm_type")) {
                    if (condMap.get(key) != null) {
                        if (condMap.get(key) == "1" || condMap.get(key).equals("1")) {
                            hql.append(key).append(" = ").append(condMap.get(key));
                        } else {
                            hql.append(key).append(" != 1 ");
                        }
                    }
                } else if (key.equals("event_type_id")) {
                    if (condMap.get(key) != null) {
                        hql.append("e.").append(key).append(" != ").append(condMap.get(key));
                    }
                } else {
                    if (condMap.get(key) == null) {
                        hql.append("e.").append(key).append(" is " + condMap.get(key));
                    } else {
                        hql.append("e.").append(key).append(" = " + condMap.get(key));
                    }
                }
                hql.append(" and ");
            }
            hql.delete(hql.lastIndexOf(" and "), hql.length()).append(" ) ");
        }
        return hql;
    }

    /*
     * (non-Javadoc)
     * @see com.sinodata.bsm.center.dao.EventDao#getAllNewEvent()
     */
    @Override
    public List<Event> getAllNewEvent() {
        StringBuilder hql = new StringBuilder("from Event t where 1=1 ");
        hql.append(" and t.status !=");
        hql.append(EventConstants.STATUS_CONFIRMED);
        return this.getSession().createQuery(hql.toString()).list();
    }

    @Override
    public List<Event> getNewEvents(Date occurTime, Integer level) {
        StringBuilder hql = new StringBuilder("from Event t where t.occurTime > ? ");
        hql.append(" and t.status !=");
        hql.append(EventConstants.STATUS_CONFIRMED);
        hql.append(" and t.level >= " + level);
        return this.getSession().createQuery(hql.toString()).setParameter(0, occurTime).list();
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<EventRemark> findEventRemarkByEventId(Long eventId) {
        StringBuilder hql = new StringBuilder("from EventRemark t where eventId= ");
        hql.append(eventId);
        return this.getSession().createQuery(hql.toString()).list();
    }

    @Override
    public Integer checkEventAck(Long[] ids) {
        StringBuilder hql = new StringBuilder("from Event t where status = 1 and id in ( ");
        for (Long id : ids) {
            hql.append(id).append(",");
        }
        hql.delete(hql.lastIndexOf(","), hql.length()).append(" )");
        return this.getSession().createQuery(hql.toString()).list().size();
    }

    /*
     * (non-Javadoc)
     * @see com.sinodata.bsm.center.dao.EventDao#findEventByConfirmEventId(java.lang.Long)
     */
    @Override
    public Event findEventByConfirmEventId(Long eventId) {
        StringBuilder hql = new StringBuilder("from Event t where confirmType =");
        hql.append(EventConstants.CONFIRM_TYPE_EVENT);
        hql.append(" and confirmer = '");
        hql.append(eventId);
        hql.append("'");
        List list = this.getSession().createQuery(hql.toString()).list();
        if (list == null || list.size() == 0) {
            return null;
        } else {
            return ((List<Event>) list).get(0);
        }
    }

    public EventCauses getEventCausesByEventId(Long eventId) {
        StringBuilder hql = new StringBuilder("from EventCauses t where eventId =");
        hql.append(eventId);
        List list = this.getSession().createQuery(hql.toString()).list();
        if (list.size() == 0) {
            return new EventCauses();
        } else {
            // EventCauses eventCauses = new EventCauses();
            // eventCauses.setId(((List<EventCauses>) list).get(0).getId());
            return ((List<EventCauses>) list).get(0);
            //return eventCauses;
        }
    }

    @Override
    public Integer checkEventConfirm(Long[] ids) {
        StringBuilder hql = new StringBuilder("from Event t where status = 2 and id in ( ");
        for (Long id : ids) {
            hql.append(id).append(",");
        }
        hql.delete(hql.lastIndexOf(","), hql.length()).append(" )");
        return this.getSession().createQuery(hql.toString()).list().size();
    }

    /**
     * 资源活动事件 查询目标为当前资源下的所有子资源(递归)
     */
    @SuppressWarnings("unchecked")
    @Override
    public Page<Object> resActiveEventPage(Page<Object> page, Condition condition, Map<String, Object> extensionParameter) {
        StringBuffer sql = new StringBuffer("  select e.res_id resId,e.event_type_id eventTypeId,e.level_ levelId,e.msg msg,to_char(e.occur_time,'yyyy-MM-dd hh24:mi:ss') occurTime,e.id id,e.status statu from event e  ");
        sql.append(" where (e.res_id in ((");
        Integer parentResId = Integer.parseInt(extensionParameter.get("resId").toString());
        sql.append(" select end_id from( select n.end_id from (  select * from res_relation t where t.relation_type_id in ( ");
        sql.append(" select id from relation_type rel_type start with rel_type.id=" + RelationConstants.FILIATION + " connect by prior rel_type.id=rel_type.parent ) ");
        sql.append(" ) n start with n.start_id=" + parentResId + " connect by  prior n.end_id =  n.start_id  ) t left join res r on t.end_id =r.id and r.deleted = 0 ");
        sql.append("  )  union select " + parentResId + " from dual)) and e.status<>2 and e.catalog= " + EventConstants.CATALOG_FAULTEVENT + " and e.filter_event_id is null");
        Map<String, String> parameter = condition.getWhere();
        Iterator<String> keys = parameter.keySet().iterator();
        while (keys.hasNext()) {
            String parameterName = keys.next(); //参数原始值 LIKE_S**
            String parameterValue = parameter.get(parameterName); //FILTER_EQ_Iprobe_Id //参数值
            String parameterType = parameterName.substring(parameterName.indexOf("_") + 1, parameterName.indexOf("_") + 2); //参数类型 S、I ...
            String conditionSymbol = parameterName.substring(0, parameterName.indexOf("_")); //条件符 LIKE、EQ ...
            String newParameterName = parameterName.substring(parameterName.indexOf("_") + 2); //参数

            int tempIndex = 0; //条件符号临时索引

            // 约束条件
            if (conditionSymbol.indexOf("EQ") != -1) {
                tempIndex = 1;
            } else if (conditionSymbol.indexOf("LIKE") != -1) {
                tempIndex = 2;
            } else if (conditionSymbol.indexOf("LT") != -1) { // 小于
                tempIndex = 3;
            } else if (conditionSymbol.indexOf("GT") != -1) { // 大于
                tempIndex = 4;
            } else if (conditionSymbol.indexOf("GE") != -1) { // 大于等于
                tempIndex = 5;
            } else if (conditionSymbol.indexOf("LE") != -1) { // 小于等于
                tempIndex = 6;
            } else if (conditionSymbol.indexOf("OR") != -1) { // 或者
                tempIndex = 7;
            } else if (conditionSymbol.indexOf("NOT") != -1) {
                tempIndex = 8;
            } else if (conditionSymbol.indexOf("IN") != -1) {
                tempIndex = 9;
            }

            if (parameterType.equals("S")) {
                parameterValue = "'" + parameterValue + "'";
            }

            switch (tempIndex) {
                case 1:
                    sql.append(" and e." + newParameterName + " = " + parameterValue + " ");
                    break;
                case 2:
                    parameterValue = "'%" + parameterValue.toString().replace("'", "") + "%'";
                    sql.append(" and e." + newParameterName + " like " + parameterValue + "");
                    break;
                case 3:
                    sql.append(" and e." + newParameterName + " < " + parameterValue + "");
                    break;
                case 4:
                    sql.append(" and e." + newParameterName + " > " + parameterValue + "");
                    break;
                case 5:
                    sql.append(" and e." + newParameterName + " >= " + parameterValue + "");
                    break;
                case 6:
                    sql.append(" and e." + newParameterName + " <= " + parameterValue + "");
                    break;
                case 7:
                    sql.append(" or e." + newParameterName + " = " + parameterValue + "");
                    break;
                case 8:
                    sql.append(" and e." + newParameterName + " <> " + parameterValue + "");
                    break;
                case 9:
                    sql.append(" and e." + newParameterName + " in (" + parameterValue + " )");
            }
        }
        sql.append(" order by  e.event_type_id asc, e.occur_time desc");
        page.setTotal(this.getTotalBySql(sql.toString()));
        List<Object> list = this.getSession().createSQLQuery(sql.toString()).setFirstResult(page.getFirstResultNo()).setMaxResults(page.getPageSize()).list();
        page.setRows(list);
        return page;
    }

    /**
     * 获取查询结果总数(sql)
     * @param sql
     * @return
     */
    public int getTotalBySql(String sql) {
        StringBuilder totalSql = new StringBuilder("select count(*) from (");
        totalSql.append(sql);
        totalSql.append(")");
        int total = Integer.parseInt(getSession().createSQLQuery(totalSql.toString()).uniqueResult().toString());
        return total;
    }

    @Override
    public List<ReduceEventDynamicBean> getDynamicEvent(String time, String reduceType) {
        StringBuffer sql = new StringBuffer("select red.reduce_Type,");
        sql.append("red.time_,");
        sql.append("red.created,");
        sql.append("red.acked,");
        sql.append("red.auto_Closed,");
        sql.append("red.man_Closed");
        sql.append(" from Reduce_Event_Dynamic red");
        sql.append(" where 1=1");
        if (reduceType != null && !reduceType.equals("")) {
            sql.append(" and red.reduce_Type='");
            sql.append(reduceType);
            sql.append("'");
        }
        if (time != null && !time.equals("")) {
            sql.append(" and red.time_='");
            sql.append(time);
            sql.append("'");
        }
        Query query = getSession().createSQLQuery(sql.toString());
        List<Object[]> list = query.list();
        List<ReduceEventDynamicBean> listResult = new ArrayList<ReduceEventDynamicBean>();
        for (Object[] o : list) {
            ReduceEventDynamicBean reduceEventDynamic = new ReduceEventDynamicBean();
            reduceEventDynamic.setReduceType(String.valueOf(o[0]));
            reduceEventDynamic.setTime(String.valueOf(o[1]));
            reduceEventDynamic.setCreated(Long.valueOf(String.valueOf(o[2])));
            reduceEventDynamic.setAcked(Long.valueOf(String.valueOf(o[3])));
            reduceEventDynamic.setAutoClosed(Long.valueOf(String.valueOf(o[4])));
            reduceEventDynamic.setManClosed(Long.valueOf(String.valueOf(o[5])));
            listResult.add(reduceEventDynamic);
        }
        return listResult;
    }

    @Override
    public List<Object[]> getActiveEventNum(String resIds) {
        StringBuffer sql = new StringBuffer("select");
        sql.append(" sum(decode(status,0,1,0)) as s0,");
        sql.append(" sum(decode(status,1,1,0)) as s1,");
        sql.append(" sum(case when status != 2 then 1 else 0 end) as l,");
        sql.append(" sum(case when status != 2 and level_ = 2 then 1 else 0 end) as l2,");
        sql.append(" sum(case when status != 2 and level_ = 3 then 1 else 0 end) as l3,");
        sql.append(" sum(case when status != 2 and level_ = 4 then 1 else 0 end) as l4,");
        sql.append(" sum(case when status != 2 and level_ = 5 then 1 else 0 end) as l5");
        sql.append(" from ( select event.id,event.status,event.level_ from event left outer join res on event.res_id=res.id where res.deleted = 0 and event.CATALOG = 1 and event.filter_event_id is null ");

        if (resIds != null && !resIds.equals("")) {
            sql.append(" and res_id").append(" in (");
            String[] str = resIds.split(",");
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < str.length; i++) {
                sb.append(str[i]).append(",");
            }
            sb.delete(sb.lastIndexOf(","), sb.length());
            sql.append(sb);
            sql.append(" )");
            sql.append(" union ");
            sql.append(" select event.id,event.status,event.level_ from event left outer join res on event.res_id=res.id where res.deleted = 0 and event.CATALOG = 1 and event.filter_event_id is null ");
            sql.append(" and res_id in (");
            sql.append(" select end_id  from res_relation start with start_id in ( ");
            sql.append(sb);
            sql.append(") and relation_type_id  in ( select  id from relation_type  start with id = 10 connect by prior  id = parent ) connect by prior  end_id = start_id");
            sql.append(")");
        }
        sql.append(" )");
        Query query = getSession().createSQLQuery(sql.toString());
        return query.list();
    }

    public List<Event> getActiveEvents(String resIds) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from ( select event.* from event left outer join res on event.res_id=res.id where res.deleted = 0 and event.CATALOG = 1 and event.filter_event_id is null and event.status < 2 ");
        if (resIds != null && !resIds.equals("")) {
            sql.append(" and res_id").append(" in (");
            sql.append(resIds);
            sql.append(" )");
            sql.append(" union ");
            sql.append(" select event.* from event left outer join res on event.res_id=res.id where res.deleted = 0 and event.CATALOG = 1 and event.filter_event_id is null and event.status < 2  ");
            sql.append(" and res_id in (");
            sql.append(" select end_id  from res_relation start with start_id in ( ");
            sql.append(resIds);
            sql.append(") and relation_type_id  in ( select  id from relation_type  start with id = 10 connect by prior  id = parent ) connect by prior  end_id = start_id");
            sql.append(")) order by level_ desc, occur_time desc ");
        }
        System.out.println(sql.toString());
        Query query = getSession().createSQLQuery(sql.toString()).addEntity(Event.class);
        return query.list();
    }
}